﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace YandexWebmaster
{
    public partial class BD
    {
        public Site GetSite(string domain)
        {
            dynamic result = GetTable("sites", $"SELECT * FROM sites WHERE domain='{domain}'")
                .AsEnumerable()
                .Select(
                    t => new Site
                    {
                        ID = Convert.ToInt32(t["id"]),
                        ID_Account = Convert.ToInt32(t["id_account"]),
                        Domain = t["domain"].ToString()
                    }
                ).Single();
            return result;
        }

        public List<Site> GetSites()
        {
            dynamic result = GetTable("sites", $"SELECT * FROM sites")
                .AsEnumerable()
                .Select(
                    t => new Site
                    {
                        ID = Convert.ToInt32(t["id"]),
                        ID_Account = Convert.ToInt32(t["id_account"]),
                        Domain = t["domain"].ToString()
                    }
                ).ToList();
            return result;
        }

        public List<Site> GetSites(Account account)
        {
            dynamic result = GetTable("sites", $"SELECT * FROM sites WHERE id_account='{account.ID}'")
                .AsEnumerable()
                .Select(
                    t => new Site
                    {
                        ID = Convert.ToInt32(t["id"]),
                        ID_Account = Convert.ToInt32(t["id_account"]),
                        Domain = t["domain"].ToString()
                    }
                ).ToList();

            return result;
        }

        public void InsertSites(List<Site> sites)
        {
            using (var connection = new SQLiteConnection($"Data Source={Directory.GetCurrentDirectory()}\\{Name};Version=3;"))
            {
                connection.Open();
                SQLiteTransaction transaction = connection.BeginTransaction();
                SQLiteCommand command = new SQLiteCommand(connection);
                
                foreach (var site in sites)
                {
                    string query = $"INSERT INTO sites (id_account, domain, in_webmaster, domain_unicode, verified, host_id)" +
                        $" SELECT " +
                        $"'{site.ID_Account}', " +
                        $"'{site.Domain}', " +
                        $"'{(site.InWebmaster == true ? 1 : 0)}', " +
                        $"'{site.DomainUnicode}', '{(site.Verified == true ? 1 : 0)}', '{site.HostID}'" +
                        $" WHERE NOT EXISTS (SELECT domain FROM sites WHERE domain='{site.Domain}')";
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }
                transaction.Commit(); //применяем изменения
                MessageBox.Show("Сайты добавлены");
            }
        }
    }
}
